package com.elitel.common.businessJdbc;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

import com.elitel.common.access.FilterStationDataSqlHandler;
import com.elitel.common.businessJdbc.mongodb.MongoSelectStatementHandler;
import com.elitel.common.exception.BusinessStatementException;
import com.elitel.frame.main.entity.constant.DictionaryConstant;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;

/** Here is for workaround for mybatis not support sql injection
 * 1. using jdbc of slave datasource , witch is for dynamic route datasource for business handle
 * 2. result existed count or List<Map>
 **/
@Data
@Component("generalBusinessSelectPreparedStatementHandler")
@Slf4j
@Primary
public class BusinessSelectPreparedStatementHandler extends BusinessPreparedStatementHandler{

    @Autowired
    private MongoSelectStatementHandler mongoSelectStatementHandler;

    private final Map<String, BusinessSelectPreparedStatementHandler> handlers = new HashMap<>();

    @PostConstruct
    private void init(){
        handlers.put(DictionaryConstant.DatabaseType_MONGODB, mongoSelectStatementHandler);

    }
    // here is pass sql , here is not existed where condition
    // maybe pass field section , table section, where condition (BusinessPrepareStatementCondition)
    // here concat sql , and concat prepareStatement sql by parse BusinessPrepareStatementCondition
    // success using general rule
    public Long rowCountByExecuteSql(BusinessSelectPreparedStatement businessPrepareStatement){
        BusinessSelectPreparedStatementHandler handler = handlers.get(businessPrepareStatement.getDatabaseType());
        if(handler != null)
            return handler.rowCountByExecuteSql(businessPrepareStatement);

        return findRowCountForRelationDatabase(businessPrepareStatement);
    }

    private Long findRowCountForRelationDatabase(BusinessSelectPreparedStatement businessPrepareStatement) {
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet rs = null;

        Long count = 0l;

        String precompileCountSql = FilterStationDataSqlHandler.doFilterSql(businessPrepareStatement.getPrecompileCountSql()
                , businessPrepareStatement.getFilterSqlHandlerDTO()
                , businessPrepareStatement.getDatabaseType());
        SqlRecorder recorder = new SqlRecorder();
        recorder.setServiceGuid(businessPrepareStatement.getServiceGuid());
        recorder.setPrecompileSql(precompileCountSql);
        try{
            // here is get connection
            connection = getConnection();
            // connection get preparestatement
            prepareStatement = connection.prepareStatement(precompileCountSql);

            handlPrecompileParameter(recorder, businessPrepareStatement, prepareStatement);

            if(recorder.canExecuteSql()){
                log.info(recorder.formatInfo());
            }else{
                log.error(recorder.formatInfo());
            }

            // preparestatement.execute
            rs = prepareStatement.executeQuery();
            if(rs.next()){
                count = (long)rs.getInt(1);
            }
            // close resource
            rs.close();
            prepareStatement.close();
            connection.close();

        }catch (Exception e){
            log.error(e.getMessage());
            try{
                if(rs != null) rs.close();
                if(prepareStatement != null) prepareStatement.close();
                if(connection != null) connection.close();
            }catch(SQLException sqlE){
                log.error(sqlE.getMessage());
            }

            // for user view in api interface
            throw new BusinessStatementException(e.getMessage(), recorder.formatInfo());
        }

        return count;
    }

    public List<Map<String, Object>> findRowListOfFieldResultByExecuteSql(BusinessSelectPreparedStatement businessPrepareStatement){
        BusinessSelectPreparedStatementHandler handler = handlers.get(businessPrepareStatement.getDatabaseType());
        if(handler != null)
            return handler.findRowListOfFieldResultByExecuteSql(businessPrepareStatement);

        return findRowListForRelationDatabase(businessPrepareStatement);
    }
    private List<Map<String, Object>> findRowListForRelationDatabase(BusinessSelectPreparedStatement businessPrepareStatement) {
        List<Map<String, Object>> fieldResultRowList = new LinkedList<>();
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet rs = null;
        //添加测站、数据时间过滤功能
        String precompileSql = FilterStationDataSqlHandler.doFilterSql(businessPrepareStatement.getPrecompileSql()
                , businessPrepareStatement.getFilterSqlHandlerDTO()
                , businessPrepareStatement.getDatabaseType());
        SqlRecorder recorder = new SqlRecorder();
        recorder.setServiceGuid(businessPrepareStatement.getServiceGuid());
        recorder.setPrecompileSql(precompileSql);
        try{
            connection = getConnection();
            prepareStatement = connection.prepareStatement(precompileSql);
            handlPrecompileParameter(recorder, businessPrepareStatement, prepareStatement);
            if(recorder.canExecuteSql()){
                log.info(recorder.formatInfo());
            }else{
                log.error(recorder.formatInfo());
            }
            rs = prepareStatement.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnLength = metaData.getColumnCount();
            while(rs.next()){
                Map<String, Object> fieldResultRow = new HashMap<>(columnLength);
                for(int i=0; i<columnLength; i++){
                    String columnName = metaData.getColumnName(i + 1);
                    Object columnValue = rs.getObject(columnName);
                    if(null == columnValue)
                        columnValue = "";

                    //处理 oracle.sql.TIMESTAMP报错
                    if (columnValue.getClass().getName().equals("oracle.sql.TIMESTAMP")){
                        Class clz = columnValue.getClass();
                        Method m = clz.getMethod("timestampValue");
                        java.sql.Timestamp t1 = (java.sql.Timestamp) m.invoke(columnValue);
                        fieldResultRow.put(columnName, t1);
                    } else {
                        fieldResultRow.put(columnName, columnValue);
                    }

                }
                fieldResultRowList.add(fieldResultRow);
            }
            rs.close();
            prepareStatement.close();
            connection.close();
        }catch (Exception e){
            log.error(e.getMessage());
            try{
                if(rs != null) rs.close();
                if(prepareStatement != null) prepareStatement.close();
                if(connection != null) connection.close();
            }catch(SQLException sqlE){
                log.error(sqlE.getMessage());
            }

            throw new BusinessStatementException(e.getMessage(), recorder.formatInfo());
        }
        return fieldResultRowList;
    }

    private void handlPrecompileParameter(SqlRecorder recorder, BusinessSelectPreparedStatement businessPrepareStatement, PreparedStatement prepareStatement){
        // handle sql , concat sql
        // here is forEach to handle
        int parameterIndex = 1;
        for(Map.Entry<String, Object> conditionEntry : businessPrepareStatement.getConditionSection().entrySet()){
            String columnName = conditionEntry.getKey().trim();
            // TODO 这里toString有点问题呀
            String columnValue = conditionEntry.getValue().toString().replaceAll("'", "").trim();
            // here is get type to match method
            String fieldNameWithoutAlias;
            if(columnName.contains("."))
                fieldNameWithoutAlias = columnName.split("\\.")[1];
            else
                fieldNameWithoutAlias = columnName;

            String fieldType = businessPrepareStatement.getFieldTypeMap().get(fieldNameWithoutAlias);
            String parameterInfo = "index = " + parameterIndex + " contionItem: " + columnName + " = " + columnValue + " fieldType = " + fieldType;

            try{
                if(matchPreparedStatementSetMethodByFieldType(businessPrepareStatement.getDatabaseType(), prepareStatement, parameterIndex++, columnValue, fieldType)){
                    recorder.addParameterInformation(parameterInfo);
                }else{
                    recorder.addParameterInformation("FAIL: " + parameterInfo);
                    recorder.addErrorMessage("FAIL: matchPreparedStatementSetMethodByFieldType match false, because of " + parameterInfo);
                }
            }catch(Exception e){
                recorder.addParameterInformation("FAIL: " + parameterInfo);
                recorder.addErrorMessage(e.getMessage());
            }
        }
    }


}
